package cn.sylinx.hbatis.db.dialect.sql;

import java.lang.reflect.Field;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import cn.sylinx.hbatis.db.common.FS;
import cn.sylinx.hbatis.db.common.FluentSqlParams;
import cn.sylinx.hbatis.db.dialect.DbType;
import cn.sylinx.hbatis.db.mapper.ModelBuilder;
import cn.sylinx.hbatis.exception.HbatisException;
import cn.sylinx.hbatis.ext.tool.RepositoryTools;
import cn.sylinx.hbatis.kit.StrKit;
import cn.sylinx.hbatis.kit.Tuple;
import cn.sylinx.hbatis.log.GLog;

public class SqlServerSqlBuilder extends DefaultSqlBuilder {

	@Override
	public String[] buildCreateTableDDL(Class<?> clz) {

		String ddl = RepositoryTools.getInstance().generateTableDdlSql(DbType.SQLSERVER, clz);
		GLog.debug("dbType:{}, ddl: {}", DbType.SQLSERVER, ddl);
		String[] ddlArray = ddl.split(";\n");
		return ddlArray;
	}

	@Override
	public String[] buildModifyTableColumnDDL(Class<?> clz, String prop) {
		String ddl = RepositoryTools.getInstance().generateModifyTableColumnDDL(DbType.SQLSERVER, clz, prop);
		if (ddl == null) {
			return null;
		}
		GLog.debug("dbType:{}, ddl: {}", DbType.SQLSERVER, ddl);
		String[] ddlArray = ddl.split(";\n");
		return ddlArray;
	}

	@Override
	public String[] buildAddTableColumnDDL(Class<?> clz, String prop) {
		String ddl = RepositoryTools.getInstance().generateAddTableColumnDDL(DbType.SQLSERVER, clz, prop);
		GLog.debug("dbType:{}, ddl: {}", DbType.SQLSERVER, ddl);
		String[] ddlArray = ddl.split(";\n");
		return ddlArray;
	}

	@Override
	protected String[] getEscapeChar() {
		return new String[] { "[", "]" };
	}

	@Override
	public Tuple buildPaginatorSql(String preSql, int pageNumber, int pageSize) {

		String reg = "[oO][rR][dD][eE][rR]( )+[bB][yY]";
		Pattern p = Pattern.compile(reg);
		String[] split = p.split(preSql);
		String orderBy = null;
		if (split.length > 1) {
			preSql = split[0];
			orderBy = "order by" + split[1];
		} else {
			orderBy = "order by id desc";
		}

		String sqlCount = "select count(1) as totalCount from (" + preSql + ") as temp";
		int offset = pageSize * (pageNumber - 1);
		StringBuilder psql = new StringBuilder();
		psql.append("select top ").append(pageSize).append(" o.* from (");
		psql.append(" select row_number() over(").append(orderBy).append(") as rownumber,* from (");
		psql.append(preSql).append(") as oo) as o where rownumber > ").append(offset);
		String pageSql = psql.toString();
		GLog.debug("sqlserver page sql: {}", pageSql);

		Object[] params = new Object[] {};
		return Tuple.apply(sqlCount, pageSql, params);
	}

	@Override
	public Tuple buildSelectSQL(FS<?> fluentSql) {

		FluentSqlParams<?> sqlParams = fluentSql.build();
		StringBuilder sql = new StringBuilder();
		if (StrKit.isNotBlank(sqlParams.getHint())) {
			sql.append(sqlParams.getHint()).append(" ");
		}

		sql.append("SELECT");
		if (sqlParams.isDistinct()) {
			sql.append(" DISTINCT");
		}
		
		if (StrKit.isNotBlank(sqlParams.getLimitSQL())) {
			sql.append(' ').append(sqlParams.getLimitSQL()).append(' ');
		}

		String singleField = null;
		Class<?> singleFieldClass = null;

		if (sqlParams.isSingleField()) {

			// 如果只取1列
			if (StrKit.isBlank(sqlParams.getSelectColumns())
					&& StrKit.isBlank(sqlParams.getSelectExpressionColumns())) {
				throw new HbatisException("need one column at least");
			}

			singleFieldClass = Object.class;

			if (StrKit.isNotBlank(sqlParams.getSelectColumns())) {
				// 首先使用selectColumns内容, 其次使用 selectExpressionColumns，native sql表达式
				singleField = sqlParams.getSelectColumns().split(",")[0].trim();

				String bingoAttr = null;
				Map<String, String> attrs = sqlParams.getAttrs();
				for (Entry<String, String> entry : attrs.entrySet()) {
					if (singleField.equals(entry.getKey()) || singleField.equals(entry.getValue())) {
						bingoAttr = entry.getKey();
					}
				}

				if (bingoAttr == null) {
					throw new HbatisException("no field specified");
				}

				Map<String, Field> fieldMap = ModelBuilder.getModelFabric(sqlParams.getModelClass()).getFieldMap();
				singleFieldClass = fieldMap.get(bingoAttr).getType();

			}

		}

		boolean hasSelected = false;
		if (StrKit.isNotBlank(sqlParams.getSelectColumns())) {
			sql.append(' ').append(sqlParams.getSelectColumns()).append(' ');
			hasSelected = true;
		} else if (sqlParams.getExcludedColumns() != null && !sqlParams.getExcludedColumns().isEmpty()) {
			sql.append(' ').append(
					ModelBuilder.buildColumnsByExcluded(sqlParams.getModelClass(), sqlParams.getExcludedColumns()))
					.append(' ');
			hasSelected = true;
		}

		if (StrKit.isNotBlank(sqlParams.getSelectExpressionColumns())) {
			// 如果表达式不为空
			sql.append(hasSelected ? "," : "").append(sqlParams.getSelectExpressionColumns());

		} else if (!hasSelected) {
			sql.append(" * ");
		}

		sql.append("FROM ").append(sqlParams.getTableName());
		if (sqlParams.getConditionSQL().length() > 0) {
			sql.append(" WHERE ").append(sqlParams.getConditionSQL().substring(FS.AND_STR.length()));
		}

		if (StrKit.isNotBlank(sqlParams.getGroupBy())) {
			sql.append(" GROUP BY").append(sqlParams.getGroupBy());
		}
		
		if (StrKit.isNotBlank(sqlParams.getOrderBy())) {
			sql.append(" ORDER BY").append(sqlParams.getOrderBy());
		}

		int len = sqlParams.getParamValues() == null || sqlParams.getParamValues().isEmpty() ? 0
				: sqlParams.getParamValues().size();
		Object[] params = null;

		if (len > 0) {
			params = new Object[len];
			sqlParams.getParamValues().toArray(params);
		}

		GLog.debug("sql:{}, params:{}", sql.toString(), params);

		return Tuple.apply(sql.toString(), params,
				(singleFieldClass == null ? sqlParams.getModelClass() : singleFieldClass));
	}
	
}
